package database;

import java.util.ArrayList;

import objects.Money;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandlerForApp extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "management_money";
	private static final String TABLE_NAME = "money";

	private static final String KEY_ID = "id";
	private static final String KEY_AMOUNT = "amount";
	private static final String KEY_DAY = "day";

	private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME
			+ "(" + KEY_ID + " INTEGER PRIMARY KEY, " + KEY_AMOUNT
			+ " INTEGER, " + KEY_DAY + " DATE)";
	private static final String DROP_TABLE = "DROP TABLE IF EXISTS "
			+ TABLE_NAME;

	public DatabaseHandlerForApp(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		db.execSQL(CREATE_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL(DROP_TABLE);
		onCreate(db);
	}

	public void addMoney(Money money) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues v = new ContentValues();
		v.put(KEY_AMOUNT, money.getMoney());
		v.put(KEY_DAY, money.getDay());

		db.insert(TABLE_NAME, null, v);
		db.close();
	}

	public ArrayList<Money> getMoneyByDay(String day) {
		ArrayList<Money> l = new ArrayList<Money>();

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cs = db.query(TABLE_NAME, new String[] { KEY_ID, KEY_AMOUNT,
				KEY_DAY }, KEY_DAY + "=?", new String[] { day }, null, null,
				KEY_DAY, null);
		if (cs.moveToFirst()) {
			do {
				Money m = new Money(Integer.parseInt(cs.getString(0)),
						Integer.parseInt(cs.getString(1)), cs.getString(2));
				l.add(m);
			} while (cs.moveToNext());
		}
		return l;
	}

	public ArrayList<Money> getMoneyByMonth(String month, String year) {
		ArrayList<Money> l = new ArrayList<Money>();
		String getMoneyByMonthQuery = "SELECT  * FROM " + TABLE_NAME
				+ " WHERE " + KEY_DAY + " LIKE " + "'" + year + "/" + month
				+ "/%' ORDER BY " + KEY_DAY;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cs = db.rawQuery(getMoneyByMonthQuery, null);
		if (cs.moveToFirst()) {
			do {
				Money m = new Money(Integer.parseInt(cs.getString(0)),
						Integer.parseInt(cs.getString(1)), cs.getString(2));
				l.add(m);
			} while (cs.moveToNext());
		}
		return l;
	}

	public int getMoneyCount() {
		String countQuery = "SELECT  * FROM " + TABLE_NAME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cs = db.rawQuery(countQuery, null);

		// return count
		return cs.getCount();
	}

	public ArrayList<Money> getAllMoney() {
		ArrayList<Money> l = new ArrayList<Money>();
		String select_all_query = "SELECT * FROM " + TABLE_NAME
				+ " ORDER BY day DESC, id DESC";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cs = db.rawQuery(select_all_query, null);
		if (cs.moveToFirst()) {
			do {
				Money m = new Money(Integer.parseInt(cs.getString(0)),
						Integer.parseInt(cs.getString(1)), cs.getString(2));
				l.add(m);
			} while (cs.moveToNext());
		}
		return l;
	}

	public String sumAllMoney() {
		String select_all_query = "SELECT SUM(" + KEY_AMOUNT + ") FROM "
				+ TABLE_NAME;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cs = db.rawQuery(select_all_query, null);
		if (cs != null)
			cs.moveToFirst();
		return cs.getString(0);
	}

	public void deleteMoneyById(String id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_NAME, KEY_ID + "=?", new String[] { id });
		db.close();
	}
}
